
package com.rx.mgt.dao;  

import java.math.BigInteger;
import java.util.List;
import java.util.Map;

import com.rx.mgt.util.StringUtil;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.rx.mgt.dao.base.ManagementHibernateDaoBase;
import com.rx.mgt.domain.UserQuotaGroup;
import com.rx.mgt.po.UsersBo;
import org.springframework.util.CollectionUtils;

/** 
 * ClassName:UuserQuotaGroupDao 
 * Function :
 * Reason   : 
 * Date     :Jan 31, 2017 9:21:45 PM 
 * @author   luolei 
 * @since    JDK 1.6
 */
@Repository
public class UserQuotaGroupDao extends ManagementHibernateDaoBase<UserQuotaGroup, Long>{
    @Transactional(readOnly=true)
    public List<UserQuotaGroup> getAllQuotaGroup(UsersBo usersBo){
        String hql= " from UserQuotaGroup au where 1=1 and au.status='1' ";
        if(StringUtils.isNotBlank(usersBo.getDomain())){
            hql=hql +" and au.name=:domain";
        }
        if(StringUtils.isNotBlank(usersBo.getName())){
            hql=hql +" and au.name=:name ";
        }
        Query query = template.getSessionFactory().getCurrentSession().createQuery(hql);
        if(StringUtils.isNotBlank(usersBo.getDomain())){
            query.setParameter("domain", usersBo.getDomain());
        }
        if(StringUtils.isNotBlank(usersBo.getName())){
            query.setParameter("name", usersBo.getName());
        }

        return query.list();
    }

    @Transactional
    public List<Map<String,Object>> getGroupByQuotaGroupName(String name) {
        String selectSql = "select ug.id as id, ug.name as name,ud.department_name as departmentName,ud.id as did   ";
        String fromSql ="from user_quota_group ug join user_group_department ud on ug.id = ud.user_quota_group_id " +
                       "where ug.status='1' and ug.name=?";
        Query squery = template.getSessionFactory().getCurrentSession().createSQLQuery(selectSql + fromSql)
                .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        squery.setString(0,name);
        return (List<Map<String,Object>>) squery.list();
    }

    @Transactional
    public List<Map<String,Object>> getGroupMembersByGroupName(String name) {
        String sql = "select ue.id,ug.name as groupName,ug.domain,ud.department_name as departMentName,ue.name as userName,ue.user_name as userEmail,ue.telephone as userPhone";
         sql+=" from user_quota_group ug  join user_info_ext ue on ug.id=ue.group_id left join user_group_department ud on ue.department_id = ud.id" +
                " where ug.name=? and ue.status='Y' and ug.status='1' and ud.status='1'";
        Query squery = template.getSessionFactory().getCurrentSession().createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        squery.setString(0,name);
        return (List<Map<String,Object>>) squery.list();

    }

    @Transactional
    public List<Map<String,Object>> getGroupMembersByDepartMentName(String name,String departmentName) {
        String sql = "select ue.id,ug.name as groupName,ug.domain,ud.department_name as departMentName,ue.name as userName,ue.user_name as userEmail,ue.telephone as userPhone";
        sql +=" from user_quota_group ug  join user_group_department ud on ug.id=ud.user_quota_group_id" +
                " join user_info_ext ue on ud.id = ue.department_id where ug.name=? and ud.department_name=? and ue.status='Y' and ug.status='1' and ud.status='1'";
         Query squery = template.getSessionFactory().getCurrentSession().createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        squery.setString(0,name);
        squery.setString(1,departmentName);
        return (List<Map<String,Object>>) squery.list();
    }
    @Transactional
    public Map<String,Object> findGroupByName(String name) {
        String sql = "select id ";
        sql += " from user_quota_group ug where ug.name=?";
        Query sqlQuery = template.getSessionFactory().getCurrentSession().createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        sqlQuery.setString(0,name);
        List list = sqlQuery.list();
        if(CollectionUtils.isEmpty(list)){
            return null;
        }else{
            return (Map<String,Object>)list.get(0);
        }
    }
    @Transactional
    public int addGroup(String name) {
        String sql = "insert into user_quota_group set name=?,status='1'";
        Query sqlQuery = template.getSessionFactory().getCurrentSession().createSQLQuery(sql);
        sqlQuery.setString(0,name);
        return sqlQuery.executeUpdate();
    }
    @Transactional
    public int updateGroup(String id, String name) {
        String sql = "update user_quota_group set name=? where id=?";
        return template.getSessionFactory().getCurrentSession().createSQLQuery(sql).setString(0,name).setString(1,id).executeUpdate();
    }
    @Transactional
    public Map<String,Object> findDepartMentByName(String pid, String name) {
        String sql = "select id from user_group_department where department_name=? and user_quota_group_id=? and status='1'";
        Query sqlQuery =template.getSessionFactory().getCurrentSession().createSQLQuery(sql).setString(0,name).setString(1,pid).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List list = sqlQuery.list();
        if(CollectionUtils.isEmpty(list)){
            return null;
        }else{
            return (Map<String,Object>)list.get(0);
        }
    }

    @Transactional
    public int addDepartMent(String name,String pid) {
        String id = StringUtil.UUID();
        String sql = "insert into user_group_department set department_name=?,id=?,status='1',user_quota_group_id=?";
        return template.getSessionFactory().getCurrentSession().createSQLQuery(sql).setString(0,name).setString(1,id).setString(2,pid).executeUpdate();
    }
    @Transactional
    public int updateDepartMent(String id, String name) {
        String sql = "update user_group_department set department_name=? where id=?";
        return  template.getSessionFactory().getCurrentSession().createSQLQuery(sql).setString(0,name).setString(1,id).executeUpdate();
    }
    @Transactional
    public long queryMaxGroupId() {
        String sql = "select max(id) as id from user_quota_group";
        Query query = template.getSessionFactory().getCurrentSession().createSQLQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List list = query.list();
        if(CollectionUtils.isEmpty(list)){
            return 1;
        }else{
            Map<String,Object> rs = (Map<String,Object>)list.get(0);
            return ((BigInteger)rs.get("id")).longValue();
        }
    }
}
  